USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetOpportunityDetails')
	BEGIN
		DROP  Procedure  dbo.GetOpportunityDetails
	END
GO

CREATE Procedure [dbo].[GetOpportunityDetails]
	@PKID		nvarchar(100) = NULL
,	@OWNERID	varchar(10)	  = '0'
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@allUsers		varchar(max)
	,	@oppTypes		varchar(max)
	,	@accounts		varchar(max)
	,	@oppStatus		varchar(max)
	,	@oppStage		varchar(max)
	,	@resellers		varchar(max)
	,	@refSources		varchar(max)
	,	@contacts		varchar(max)
	,	@relations		varchar(max)

	SELECT	@sql = ''
		,	@allUsers		= dbo.GetAllUsersXml()
		,	@oppTypes		= dbo.GetOpportunityTypeXML()
		,	@accounts		= dbo.GetAccountsXml(@OWNERID)
		,	@oppStatus		= dbo.GetStatusXml('Opportunity')
		,	@oppStage		= dbo.GetOpportunityStageXml()
		,	@resellers		= dbo.GetResellersXml()
		,	@refSources		= dbo.GetOpportunityReferralXml()
		,	@contacts		= dbo.GetContactsXml('')
		,	@relations		= dbo.GetRelationsXML(@PKID)


	;WITH Opp AS (
		-- OPPORTUNITYH Table
		SELECT 
			OpportunityId
		,	[Description]
		,	OppType				AS OppTypeID
		,	OppRevenue			
		,	Probability
		,	TotalRevenue
		,	TotalQuantity
		,	ISNULL(CONVERT(VARCHAR(10), CloseDate, 101),'')			AS CloseDate
		,	ISNULL(CONVERT(VARCHAR(10), MileStoneDate, 101),'')		As MilestoneDate
		,	[Status]			AS [StatusID]
		,	[Application]
		,	OppStage			AS [OppStageID]
		,	Competitor
		,	DealStatus			AS DealStatusID
		,	ResellerNo
		,	OppReferral			AS OppReferralID
		,	OppGuid				AS PKID
		FROM TR_OPPORTUNITYH as oph WITH (NOLOCK)
		WHERE
			CAST(oph.OppGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			0
		,	''
		,	'0'					AS OppTypeID
		,	''			
		,	''
		,	''
		,	''
		,	''
		,	''
		,	''					AS [StatusID]
		,	''
		,	''					AS [OppStageID]
		,	''
		,	''
		,	''
		,	''
		,	NEWID()				AS PKID
		WHERE
			ISNULL(@PKID, '') = ''
	), oppNotes AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN Opp as a with (NOLOCK)
			on a.PKID = n.SourceGUID
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @allUsers	
		UNION
		SELECT 2.2 as ID, @oppTypes	
		UNION
		SELECT 2.3 as ID, @accounts	
		UNION
		SELECT 2.4 as ID, @oppStatus	
		UNION
		SELECT 2.5 as ID, @oppStage	
		UNION
		SELECT 2.6 as ID, @resellers	
		UNION
		SELECT 2.7 as ID, @refSources	
		UNION
		SELECT 2.8 as ID, @contacts	
		UNION
		SELECT 2.9 as ID, @relations	
		UNION
		SELECT
			3 AS ID, 
			'<PKID><![CDATA[' + ISNULL(CAST(A.PKID AS VARCHAR(40)), '') + ']]></PKID>'
		+	'<OPPORTUNITYID><![CDATA['+ ISNULL( CAST(A.OpportunityId AS VARCHAR),'') +']]></OPPORTUNITYID>'
		+	'<OPPORTUNITYGUID><![CDATA[' + ISNULL(CAST(A.PKID AS VARCHAR(40)), '') + ']]></OPPORTUNITYGUID>'
		+	'<DESCRIPTION><![CDATA['+ ISNULL(A.DESCRIPTION,'') +']]></DESCRIPTION>'
		+	'<OPPTYPEID><![CDATA['+ ISNULL( CAST(A.OppTypeID AS VARCHAR),'') +']]></OPPTYPEID>'

		+	'<OPPREVENUE><![CDATA['+ ISNULL( CAST(A.OppRevenue AS VARCHAR),'') +']]></OPPREVENUE>'
		+	'<PROBABILITY><![CDATA['+ ISNULL( CAST(A.Probability AS VARCHAR),'') +']]></PROBABILITY>'
		+	'<TOTALREVENUE><![CDATA['+ ISNULL( CAST(A.TotalRevenue AS VARCHAR),'') +']]></TOTALREVENUE>'
		+	'<TOTALQUANTITY><![CDATA['+ ISNULL( CAST(A.TotalQuantity AS VARCHAR),'') +']]></TOTALQUANTITY>'

		+	'<CLOSEDATE><![CDATA['+ ISNULL( CONVERT(VARCHAR(10), A.CloseDate, 101),'') +']]></CLOSEDATE>'
		+	'<MILESTONEDATE><![CDATA['+ ISNULL( CONVERT(VARCHAR(10), A.MileStoneDate, 101),'') +']]></MILESTONEDATE>'
		+	'<STATUSID><![CDATA['+ ISNULL( CAST(A.STATUSID AS VARCHAR),'') +']]></STATUSID>'
		+	'<APPLICATION><![CDATA['+ ISNULL( A.Application,'') +']]></APPLICATION>'
		+	'<OPPSTAGEID><![CDATA['+ ISNULL( CAST(A.OppStageID AS VARCHAR),'') +']]></OPPSTAGEID>'
		+	'<COMPETITOR><![CDATA['+ ISNULL( A.Competitor,'') +']]></COMPETITOR>'

		+	'<DEALSTATUSID><![CDATA['+ ISNULL( CAST(A.DEALSTATUSID AS VARCHAR),'') +']]></DEALSTATUSID>'
		+	'<RESELLERNO><![CDATA['+ ISNULL( CAST(A.ResellerNo AS VARCHAR),'') +']]></RESELLERNO>'
		+	'<OPPREFERRALID><![CDATA['+ ISNULL( CAST(A.OPPREFERRALID AS VARCHAR),'') +']]></OPPREFERRALID>'

		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
					AS Details
		FROM opp as A
		LEFT OUTER JOIN OppNotes as n
			on n.SourceGUID = A.PKID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END
GO

-- GetOpportunityDetails '',1
-- select dbo.GetResellersXml('')